﻿Imports System.Data
Imports System.Data.SqlClient
Public Class SinhVienDAO
    Private Shared lenhInsert As SqlCommand
    Private Shared lenhUpdate As SqlCommand
    Private Shared lenhDelete As SqlCommand
    Private dataProvider As DataProvider
    Private sqlstr As String
    Public Sub New()
        dataProvider = New DataProvider
    End Sub
    Public Shared Function GetData() As DataTable
        Dim dt As DataTable = dataProvider.Executequery("sp_XuatSV")
        Return dt
    End Function
    Public Shared Sub ThemSV(ByVal sv As SinhVienDTO)
        Dim sql As String

        sql = "INSERT INTO SINHVIEN(MaSinhVien,HoTen,SoDienThoai,GioiTinh,NgaySinh,MaLop) VALUES(@MaSinhVien,@HoTen,@SoDienThoai,@GioiTinh,@NgaySinh,@MaLop)"
        lenhInsert = New SqlCommand()
        lenhInsert.CommandText = sql
        Dim thamSo As SqlParameter

        thamSo = New SqlParameter("@MaSinhVien", SqlDbType.VarChar)
        thamSo.SourceColumn = sv.MaSinhVien
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@HoTen", SqlDbType.NVarChar)
        thamSo.SourceColumn = sv.HoTen
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@GioiTinh", SqlDbType.NVarChar)
        thamSo.SourceColumn = sv.GioiTinh
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@NgaySinh", SqlDbType.DateTime)
        thamSo.SourceColumn = sv.NgaySinh
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@SoDienThoai", SqlDbType.VarChar)
        thamSo.SourceColumn = sv.SoDienThoai
        lenhInsert.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@MaLop", SqlDbType.Int)
        thamSo.SourceColumn = sv.MaLop
        lenhInsert.Parameters.Add(thamSo)

    End Sub
    Public Shared Sub UpDateSV(ByVal sv As SinhVienDTO)
        Dim sql As String

        sql = "UPDATE SINHVIEN SET HoTen=@HoTen,SoDienThoai=@SoDienThoai,GioiTinh=@GioiTinh,NgaySinh=@NgaySinh,MaLop=@MaLop WHERE MaSinhVien=@MaSinhVien"
        lenhUpdate = New SqlCommand()
        lenhUpdate.CommandText = sql
        Dim thamSo As SqlParameter

        thamSo = New SqlParameter("@MaSinhVien", SqlDbType.VarChar)
        thamSo.SourceColumn = sv.MaSinhVien
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@HoTen", SqlDbType.NVarChar)
        thamSo.SourceColumn = sv.HoTen
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@GioiTinh", SqlDbType.NVarChar)
        thamSo.SourceColumn = sv.GioiTinh
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@NgaySinh", SqlDbType.DateTime)
        thamSo.SourceColumn = sv.NgaySinh
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@SoDienThoai", SqlDbType.VarChar)
        thamSo.SourceColumn = sv.SoDienThoai
        lenhUpdate.Parameters.Add(thamSo)

        thamSo = New SqlParameter("@MaLop", SqlDbType.Int)
        thamSo.SourceColumn = sv.MaLop
        lenhUpdate.Parameters.Add(thamSo)
    End Sub
    Public Shared Sub DeleteSV(ByVal qlSVDTO As SinhVienDTO)
        Dim sql As String

        sql = "DELETE FROM  SINHVIEN WHERE MaSinhVien=@MaSinhVien"
        lenhDelete = New SqlCommand()
        lenhDelete.CommandText = sql
        Dim thamSo As SqlParameter

        thamSo = New SqlParameter("@MaSinhVien", SqlDbType.VarChar)
        thamSo.SourceColumn = "MaSinhVien"
        lenhDelete.Parameters.Add(thamSo)
    End Sub
    Public Shared Sub CapNhat(ByVal bangSinhVien As DataTable)
        'dataProvider.GhiBang(bangSinhVien, "SINHVIEN", lenhInsert, lenhUpdate, lenhDelete)
        dataProvider.GhiBang(bangSinhVien, "SINHVIEN")
    End Sub

    Public Shared Function LayDSSinhVien() As DataTable
        Dim bangSinhVien As DataTable = dataProvider.DocBang("SELECT MaSinhVien,HoTen,GioiTinh,NgaySinh,SoDienThoai,SV.MaLop AS MaLop, TenLop FROM SINHVIEN SV,LOPHOC LH WHERE SV.MaLop=LH.MaLop")
        Return bangSinhVien
    End Function

    Public Shared Function LayCauTrucBang() As DataTable
        Return dataProvider.DocCauTruc("SELECT * FROM SINHVIEN")
    End Function

    Public Function InsertUpdateSinhVien(ByVal svDTO As SinhVienDTO, ByVal flag As Boolean)
        Dim MaSinhVien As String = svDTO.MaSinhVien
        Dim HoTen As String = svDTO.HoTen
        Dim GioiTinh As String = svDTO.GioiTinh
        Dim NgaySinh As Date = svDTO.NgaySinh
        Dim SoDienThoai As String = svDTO.SoDienThoai
        Dim MaLop As Integer = svDTO.MaLop

        If (flag = True) Then
            sqlstr = "INSERT INTO SINHVIEN(MaSinhVien,HoTen,SoDienThoai,GioiTinh,NgaySinh,MaLop) VALUES(@MaSinhVien,@HoTen,@SoDienThoai,@GioiTinh,@NgaySinh,@MaLop)"
        Else
            sqlstr = "UPDATE SINHVIEN SET HoTen=@HoTen,SoDienThoai=@SoDienThoai,GioiTinh=@GioiTinh,NgaySinh=@NgaySinh,MaLop=@MaLop WHERE MaSinhVien=@MaSinhVien"
        End If
        Return dataProvider.Excute(sqlstr)
    End Function
End Class
